﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data.SqlClient;
using System.Threading;

/// <summary>
/// SqlHelper 的摘要描述
/// </summary>
/// 
namespace com.pro2e.console.generic.util
{
    public class SqlHelper
    {
        log4net.ILog log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

        private string connStr;
        private SqlConnection connection;
        private SqlCommand command;


        public SqlHelper(string connectString)
        {
            init(connectString);
        }

        public SqlHelper(SqlConnection conn)
        {            
            connection = conn;
            command = new SqlCommand();
            command.Connection = connection;                        
        }

        private void init(string connectString)
        {
            connStr = connectString;
            connection = new SqlConnection(connStr);
            command = new SqlCommand();
            command.Connection = connection;            
        }

        public void Open()
        {
            lock (this)
            {
                if (connection.State != ConnectionState.Open)
                {
                    connection.Open();
                }
            }
        }

        public void Close()
        {
            lock (this)
            {
                command.Parameters.Clear();
                if (connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }
        }

        public static SqlParameter ToSqlParameter(string parameterName, object value) 
        {
            if (value == null)
            {
                return new SqlParameter(parameterName, DBNull.Value);
            }
            else 
            {
                return new SqlParameter(parameterName, value);
            }
        }

        public int ExecuteNonQuery(string strSQL)
        {
            int ret = 0;
            try
            {
                this.Open();
                command.CommandText = strSQL;
                ret = command.ExecuteNonQuery();
            }
            catch (System.Exception ex)
            {
                throw new Exception(ex.Message);
            }
            this.Close();

            return ret;
        }

        public object ExecuteScalar(string strSQL)
        {

            object ret = null;
            try
            {
                this.Open();
                command.CommandText = strSQL;                
                ret = command.ExecuteScalar();
                this.Close();
            }
            catch (System.Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                this.Close();
            }
            return ret;

        }

        public DataTable GetDataTable(string strSQL)
        {
            DataTable DT = new DataTable();
            try
            {
                this.Open();
                command.CommandText = strSQL;
                SqlDataAdapter DataAdapter = new SqlDataAdapter(command);
                DataAdapter.Fill(DT);

            }
            catch (System.Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                this.Close();
            }
            return DT;
        }

        public DataTable GetDataTable(string strSQL, SqlParameter[] cmdParms)
        {

            DataTable dt = new DataTable();
            using (SqlConnection connection = new SqlConnection(connStr))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    try
                    {
                        PrepareCommand(cmd, connection, null, strSQL, cmdParms);
                        SqlDataAdapter DataAdapter = new SqlDataAdapter(cmd);
                        DataAdapter.Fill(dt);
                        return dt;
                    }
                    catch (System.Data.SqlClient.SqlException ex)
                    {
                        throw ex;
                    }
                }
            }
        }
        
        /*
        public DataSet GetDataSet(string strSQL)
        {
            DataSet DS = new DataSet();
            try
            {
                this.Open();
                command.CommandText = strSQL;
                SqlDataAdapter DataAdapter = new SqlDataAdapter(command);
                DataAdapter.Fill(DS);

            }
            catch (System.Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                this.Close();
            }

            return DS;
        }
         */ 
        /*
        public SqlDataReader GetReader(string strSQL)
        {
            SqlDataReader r = null;
            try
            {
                this.Open();
                command.CommandText = strSQL;
                r = command.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (System.Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return r;
        }
        */
        public SqlDataAdapter GetSqlDataAdapter(string strSqlCmd)
        {
            SqlConnection conn = new SqlConnection(connStr);
            SqlCommand cmd = new SqlCommand();
            SqlDataAdapter adData = new SqlDataAdapter();
            try
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandText = strSqlCmd;
                adData.SelectCommand = cmd;
            }

            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }
            return adData;
        }

        private void AttachParams(ref SqlCommand cmd, SqlParameter[] paras)
        {
            foreach (SqlParameter para in paras)
            {
                if (para.Value == null)
                {
                    para.Value = System.DBNull.Value;
                }
                cmd.Parameters.AddWithValue(para.ParameterName, para.Value);
            }
        }

        public int ExecuteNonQuery(string strSqlCmd, SqlParameter[] cmdParms)
        {
            using (SqlConnection connection = new SqlConnection(connStr))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    try
                    {
                        PrepareCommand(cmd, connection, null, strSqlCmd, cmdParms);
                        int rows = cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                        return rows;
                    }
                    catch (System.Data.SqlClient.SqlException ex)
                    {
                        throw ex;
                    }
                }
            }
        }


        private void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
            {
                cmd.Transaction = trans;
            }
            cmd.CommandType = CommandType.Text;
            //cmdType; 
            if (cmdParms != null)
            {
                foreach (SqlParameter parm in cmdParms)
                {
                    cmd.Parameters.Add(parm);
                }
            }
        }

        private void PrepCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
            {
                cmd.Transaction = trans;
            }
            cmd.CommandType = CommandType.StoredProcedure;
            //cmdType; 
            if (cmdParms != null)
            {
                foreach (SqlParameter parm in cmdParms)
                {
                    cmd.Parameters.Add(parm);
                }
            }
        } 

    }
}